<?php
/**
 * SocialEngine
 *
 * @category   Application_Extensions
 * @package    Rate
 * @copyright  Copyright Hire-Experts LLC
 * @license    http://www.hire-experts.com
 * @version    $Id: Votes.php 2010-07-02 19:53 michael $
 * @author     Michael
 */

/**
 * @category   Application_Extensions
 * @package    Rate
 * @copyright  Copyright Hire-Experts LLC
 * @license    http://www.hire-experts.com
 */

class Rate_Model_DbTable_Votes extends Engine_Db_Table {
	public function fetchMostRated($limit = 5, $votes = 1, $period = 'all') {
		$tbl_name = $this->info ( 'name' );
		$select = $this->select ()->setIntegrityCheck ( false )->from ( array ('t1' => $tbl_name ), array ('page_id' => new Zend_Db_Expr ( 't1.page_id' ) ) )->joinLeft ( array ('t2' => $tbl_name ), new Zend_Db_Expr ( 't1.page_id = t2.page_id' ), array ('item_score' => new Zend_Db_Expr ( 'AVG(t2.rating)' ), 'item_count' => new Zend_Db_Expr ( 'COUNT(t2.page_id)' ) ) )->group ( 'page_id' )->having ( 'item_count >= ?', $votes )->order ( 'item_score DESC' )->order ( 'item_count DESC' )->limit ( $limit );
		
		if ($period == 'month') {
			$select->where ( 't1.creation_date > ?', new Zend_Db_Expr ( 'DATE_SUB(NOW(), INTERVAL 1 MONTH)' ) );
		} elseif ($period == 'week') {
			$select->where ( 't1.creation_date > ?', new Zend_Db_Expr ( 'DATE_SUB(NOW(), INTERVAL 1 WEEK)' ) );
		}
		
		$mostRates = $this->getAdapter ()->fetchAll ( $select );
		
		return $mostRates;
	}
	
	public function fetchMostReviewed($limit = 5, $votes = 1, $period = 'all') {
		$tbl_name = $this->info ( 'name' );
		$select = $this->select ()->setIntegrityCheck ( false )->from ( array ('t1' => $tbl_name ), array ('page_id' => new Zend_Db_Expr ( 't1.page_id' ) ) )->joinLeft ( array ('t2' => $tbl_name ), new Zend_Db_Expr ( 't1.page_id = t2.page_id' ), array ('item_score' => new Zend_Db_Expr ( 'AVG(t2.rating)' ), 'item_count' => new Zend_Db_Expr ( 'COUNT(t2.page_id)' ) ) )->group ( 'page_id' )->having ( 'item_count >= ?', $votes )->order ( 'item_count DESC' )->order ( 'item_score DESC' )->limit ( $limit );
		
		if ($period == 'month') {
			$select->where ( 't1.creation_date > ?', new Zend_Db_Expr ( 'DATE_SUB(NOW(), INTERVAL 1 MONTH)' ) );
		} elseif ($period == 'week') {
			$select->where ( 't1.creation_date > ?', new Zend_Db_Expr ( 'DATE_SUB(NOW(), INTERVAL 1 WEEK)' ) );
		}
		
		$mostReviewed = $this->getAdapter ()->fetchAll ( $select );
		
		return $mostReviewed;
	}
}